# -*- coding: utf-8 -*-
from odoo import api, fields, models
from xlrd import open_workbook
import base64
from odoo.exceptions import UserError,ValidationError
import datetime

XL_CELL_TEXT = 1
XL_CELL_NUMBER = 2

class click_to_import_orders(models.Model):
    _name = 'click_to_import.orders'

    excel_file = fields.Binary(string=u"上傳EXCEL檔案")

    def button_import(self):
        if not self.excel_file:
            raise UserError(u"檔案錯誤,沒有上傳正確的Excel File")

        xls = open_workbook(file_contents=base64.decodestring(self.excel_file))
        sheet = xls.sheet_by_index(0)
        # sheet = xls.sheets()[1] #可選Sheet

        consumer_id = self.env['res.partner']
        order_rec = self.env['sale.order']
        related_order=self.env['sale.order'].search([])
        all_order_relate=[]
        product_data = []
        today = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        product_code=self.env['product.template']
        product_quantity=''
        product_price=''
        recipient=''
        recipient_phone=''
        recipient_address=''
        client_order_ref=''
        carrier=''
        remarks=''
        sales_man = self.env['res.users']

        for r in related_order:    #撈出所有客戶關聯單號
            if r.client_order_ref :
                all_order_relate.append(r.client_order_ref)

        for row in range(1, sheet.nrows):  #讀到最後一列
            cell = sheet.cell(row, 9)  # 客戶編號
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                consumer_id = self.env['res.partner'].search([('ref','=',str(cell.value.encode('utf-8')))])
                if not consumer_id:
                    raise ValidationError(u'錯誤！第%s列的客戶資料未建立' % (row))

            cell = sheet.cell(row, 0)  # 產品編號
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                product_code = self.env['product.template'].search([('default_code', '=', str(cell.value.encode('utf-8')))])
                if not product_code:
                    raise ValidationError(u'錯誤！第%s列的產品編號未建立' % (row))

            cell = sheet.cell(row, 1)  # 數量
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                product_quantity = u'' + str(cell.value)

            cell = sheet.cell(row, 2)  # 價格
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                product_price = u'' + str(cell.value)

            cell = sheet.cell(row, 3)  # 收件人
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                recipient = str(cell.value.encode('utf-8'))

            cell = sheet.cell(row, 4)  # 收件人電話
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                recipient_phone = u'' + str(cell.value)

            cell = sheet.cell(row, 5)  # 收件人地址
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                recipient_address = str(cell.value.encode('utf-8'))

            cell = sheet.cell(row, 6)  # 客戶關聯單號
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                client_ref = u'' + str(cell.value)

            cell = sheet.cell(row, 7)  # 運輸方式
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                carrierr = self.env['delivery.carrier'].search([('name', '=', str(cell.value.encode('utf-8')))])
                if not carrierr:
                    raise ValidationError(u'錯誤！第%s列的運輸方式未建立' % (row))

            cell = sheet.cell(row, 8)  # 備註
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                remarks = str(cell.value.encode('utf-8'))

            cell = sheet.cell(row, 10)  # 業務員
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                sales_man=self.env['res.users'].search([('name', '=', str(cell.value.encode('utf-8')))])
                if not sales_man:
                    raise ValidationError(u'錯誤！第%s列的業務員未建立' % (row))

            if client_order_ref not in all_order_relate:
                if consumer_id:#(type2已建立的使用者及未建立的客戶關聯單)
                    r = []
                    r.append([0, 0, {
                        'price_unit': product_price,
                        'product_id': product_code.id,
                        'product_uom_qty': product_quantity,
                        'name': '[' + product_code.default_code + ']' + product_code.name}])

                    product_data = order_rec.create({
                        'partner_id': consumer_id.id,
                        'partner_invoice_id': consumer_id.id,
                        'partner_shipping_id': consumer_id.id,
                        'remarks':'收貨人姓名:' + str(recipient) + '\n收貨人電話:' + str(recipient_phone) + '\n收貨地址:' + str(recipient_address),
                        'carrier_id': carrierr.id,
                        'key_in_user': self.env.uid,
                        'client_ref': client_ref,
                        'user_id': sales_man.id,
                        #'price_list': 6,
                        'date_order':datetime.datetime.strptime(today,"%Y-%m-%d %H:%M:%S") ,
                        'commitment_date': datetime.datetime.strptime(today,"%Y-%m-%d %H:%M:%S") ,
                        'confirmation_date': datetime.datetime.strptime(today,"%Y-%m-%d %H:%M:%S") ,
                        'order_line': r})
                all_order_relate.append(client_order_ref)
            elif client_order_ref in all_order_relate:#(type3已建立的使用者及已建立的客戶關聯單;僅做訂單產品one2many增加)
                r = []
                r.append([0, 0, {
                    'price_unit': product_price,
                    'product_id': product_code.id,
                    'product_uom_qty': product_quantity,
                    'name': '[' + product_code.default_code + ']' + product_code.name}])
                find_relate_order=self.env['sale.order'].search([('client_order_ref', '=', client_order_ref)])
                find_relate_order.update({'order_line': r})
            if product_data != []:
                product_data.delivery_set()                   #自動設置運費(原生模組的function)
                # product_data.update({'state':'sale'})         #將狀態更新為銷售訂單(原為報價)